package com.decent.ejfadmin.report.dao.provider;

import com.decent.ejfadmin.common.bean.Page;
import com.decent.ejfadmin.report.entity.OperatorReportDTO;
import com.decent.ejfadmin.report.request.ReportQueryParam;
import com.decent.ejfadmin.utils.SQLWrapUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;
import java.util.Objects;

/**
 * @author weicx
 * @date 2018/08/01
 */
public class CollectCardReportDaoProvider {

    /**
     * 销卡地区统计计数
     *
     * @param params 参数
     * @return sql
     */
    public String queryAreaReportCount(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("SELECT count(1) FROM ( ");
        generateAreaSql(sql, params);
        sql.append(") t2 ");
        return sql.toString();
    }

    /**
     * 销卡地区统计
     */
    public String queryAreaReport(HashMap<String, Object> params) {
        ReportQueryParam queryBean = (ReportQueryParam) params.get("queryBean");
        StringBuffer sql = new StringBuffer();
        generateAreaSql(sql, params);
        Page page = (Page) params.get("page");
        if (page == null) {
            return sql.toString();
        }
        return SQLWrapUtils.wrapPage(sql, page.getBeginIndex(), page.getSize());
    }


    /**
     * 销卡地区统计组织sql
     *
     * @param sql    sql
     * @param params 参数
     */
    private void generateAreaSql(StringBuffer sql, HashMap<String, Object> params) {
        ReportQueryParam queryBean = (ReportQueryParam) params.get("queryBean");
        sql.append("SELECT\n" +
                "\tm.productType,\n" +
                "m.card_operator_name productTypeName,\n" +
                "m.card_type_name cardTypeName,\n" +
                "\tm.area,\n" +
                "\tsum(settlePrice) as settlePrice\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt.productType,\n" +
                "co.card_operator_name,\n" +
                "ct.card_type_name,\n" +
                "\t\tIF (supplyArea = 0, '全国', area) AS area,\n" +
                "\t\tsum(t.settlePrice) AS settlePrice\n" +
                "\tFROM\n" +
                "\t\tyuhuang_supply_card t,yuhuang_card_operator co,\n" +
                "yuhuang_card_type ct\n" +
                "\tWHERE\n" +
                "t.productType=co.card_operator_id\n" +
                "AND co.card_type_id=ct.card_type_id\n" +
                "\tAND\n" +
                "\t\tt.startTime >= #{queryBean.startTime} \n" +
                "\tAND t.startTime <= #{queryBean.endTime} \n");

        if (queryBean.getProductType() != null) {
            sql.append(" and t.productType=#{queryBean.productType} ");
        }
        if (queryBean.getCardType() != null) {
            sql.append(" and  co.card_type_id=#{queryBean.cardType} ");
        }
        String areaAationwide = "全国";
        String areaProvince = "省卡";
        if (StringUtils.isNotBlank(queryBean.getArea())) {
            if (StringUtils.equals(areaAationwide, queryBean.getArea())) {
                sql.append(" and supplyArea=0 ");
            } else if (StringUtils.equals(areaProvince, queryBean.getArea())) {
                sql.append(" and supplyArea=1 ");
            } else {
                sql.append(" and supplyArea=1 and area=#{queryBean.area} ");
            }
        }

        sql.append("\tAND t.`status` = 1\n" +
                "\tGROUP BY\n" +
                "\t\tt.productType,\n" +
                "\t\tt.area,\n" +
                "\t\tt.supplyArea\n" +
                "\t) m\n" +
                "GROUP BY\n" +
                "\tm.productType,\n" +
                "\tm.area\n" +
                "ORDER BY\n" +
                "\tm.productType,\n" +
                "\tm.area");
    }

    /**
     * 收卡运营商统计计数计数
     *
     * @param params 参数
     * @return sql
     */
    public String queryOperatorReportCount(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("select count(main.rowCount) from (select count(1) as rowCount ");
        generateOperatorSql(sql, params);
        sql.append(" GROUP BY sc.productType,sc.face) main ");
        return sql.toString();
    }

    /**
     * 收卡运营商统计计数组织sql
     *
     * @param sql    sql
     * @param params 参数
     */
    private void generateOperatorSql(StringBuffer sql, HashMap<String, Object> params) {
        OperatorReportDTO operatorReport = (OperatorReportDTO) params.get("operatorReport");
        sql.append(" from yuhuang_supply_card sc ,yuhuang_card_operator cp " +
                " WHERE sc.productType = cp.card_operator_id and sc.startTime >= #{operatorReport.startTime} and sc.startTime < #{operatorReport.endTime} ");

        if (Objects.nonNull(operatorReport)) {
            //运营商
            if (Objects.nonNull(operatorReport.getProductType())) {
                sql.append(" and sc.productType = #{operatorReport.productType} ");
            }
            //面值
            if (Objects.nonNull(operatorReport.getFace())) {
                sql.append(" and sc.face = #{operatorReport.face} ");
            }
        }
    }

    /**
     * 收卡运营商统计计数
     *
     * @param params 参数
     * @return sql
     */
    public String queryOperatorReport(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("select main.productType,main.card_operator_name,main.face,sum(main.preCount) as preCount,")
                .append("sum(main.dealingCount) as dealingCount,sum(main.successCount) as successCount,sum(main.waitSettlementCount) as waitSettlementCount,sum(main.waitSettlementMoney) as waitSettlementMoney,")
                .append("sum(main.failCount) as failCount,sum(main.fixCount) as fixCount,sum(main.total) as total,")
                .append("sum(main.totalFace) as totalFace,sum(main.settlePrice) as settlePrice from (SELECT sc.productType,cp.card_operator_name,")
                .append("sc.face,case sc.added when 1 then count(1) else 0 end as fixCount,")
                //在售+慢速试充
                .append("case sc.status when 0 then count(1) when 8 then count(1) else 0 end as dealingCount,")
                .append("case sc.status when 2 then count(1) else 0 end as failCount,")
                .append("case sc.status when 9 then count(1) else 0 end as waitSettlementCount,")
                .append("case sc.status when 9 then SUM(settlePrice) else 0 end as waitSettlementMoney,")
                //可售+等待慢速试充
                .append("case sc.status when 3 then count(1) when 7 then count(1) else 0 end as preCount,")
                .append("case sc.status when 1 then case added when 0 then count(1) else 0 end else 0 end as successCount,")
                .append("count(1) AS total,sum(face) AS totalFace,sum(settlePrice) AS settlePrice ");

        generateOperatorSql(sql, params);

        sql.append(" GROUP BY productType,face, sc.status, added ORDER BY productType,face) main group by ")
                .append("main.productType,main.face ORDER BY main.productType,main.face ");
        Page page = (Page) params.get("pageUnit");
        return SQLWrapUtils.wrapPage(sql, page.getBeginIndex(), page.getSize());
    }

    /**
     * 收卡运营商统计计数
     *
     * @param params 参数
     * @return sql
     */
    public String queryEquityOperatorReport(HashMap<String, Object> params) {
        OperatorReportDTO operatorReport = (OperatorReportDTO) params.get("operatorReport");
        StringBuffer sql = new StringBuffer(100);
        sql.append("SELECT '权益产品' cardOperatorName,sum(t.face)  as face,sum(t.preCount) as preCount,sum(t.dealingCount) as dealingCount,sum(t.successCount) as successCount, " +
                "sum(t.waitSettlementCount) as waitSettlementCount,sum(t.waitSettlementMoney) as waitSettlementMoney,sum(t.failCount) as failCount, " +
                "sum(t.fixCount) as fixCount,sum(t.total) as total,sum(t.totalFace) as totalFace,sum(t.settlePrice) as settlePrice " +
                "from ( select main.productType,main.card_operator_name,main.face,sum(main.preCount) as preCount,sum(main.dealingCount) as dealingCount, " +
                "sum(main.successCount) as successCount,sum(main.waitSettlementCount) as waitSettlementCount,sum(main.waitSettlementMoney) as waitSettlementMoney, " +
                "sum(main.failCount) as failCount,sum(main.fixCount) as fixCount,sum(main.total) as total,sum(main.totalFace) as totalFace, " +
                "sum(main.settlePrice) as settlePrice from (SELECT sc.productType,cp.card_operator_name,sc.face,case sc.added when 1 then count(1) else 0 end as fixCount, " +
                "case sc.status when 0 then count(1) when 8 then count(1) else 0 end as dealingCount,case sc.status when 2 then count(1) else 0 end as failCount, " +
                "case sc.status when 9 then count(1) else 0 end as waitSettlementCount,case sc.status when 9 then SUM(settlePrice) else 0 end as waitSettlementMoney, " +
                "case sc.status when 3 then count(1) when 7 then count(1) else 0 end as preCount, " +
                "case sc.status when 1 then case added when 0 then count(1) else 0 end else 0 end as successCount,count(1) AS total,sum(face) AS totalFace, " +
                "sum(settlePrice) AS settlePrice ");
        sql.append(" from yuhuang_supply_card sc ,yuhuang_card_operator cp " +
                " WHERE sc.productType = cp.card_operator_id and sc.startTime >= #{operatorReport.startTime} and sc.startTime < #{operatorReport.endTime} ");
        sql.append(" and sc.productType >310 ");
        if (Objects.nonNull(operatorReport)) {
            if (Objects.nonNull(operatorReport.getFace())) {
                sql.append(" and sc.face = #{operatorReport.face} ");
            }
        }
        sql.append(" GROUP BY productType,face, sc.status, added ORDER BY productType,face) main " +
                "group by main.productType,main.face ORDER BY main.productType,main.face )t  ");
        Page page = (Page) params.get("pageUnit");
        return SQLWrapUtils.wrapPage(sql, page.getBeginIndex(), page.getSize());
    }

    /**
     * 收卡销卡统计计数
     *
     * @param params 查询bean
     * @return sql
     */
    public String actualSupplyCardReportCount(HashMap<String, Object> params) {
        StringBuilder sql = new StringBuilder(100);
        sql.append("SELECT count(1) FROM (SELECT 1 ");
        generateReportActualSupplyCardSql(sql, params);
        sql.append(") t2");
        return sql.toString();
    }

    /**
     * 分页查询收卡销卡统计组织sql
     *
     * @param sql    sql
     * @param params 参数
     */
    private void generateReportActualSupplyCardSql(StringBuilder sql, HashMap<String, Object> params) {
        ReportQueryParam reportQueryParam = (ReportQueryParam) params.get("reportQueryParam");
        sql.append("FROM (SELECT u.account,\n" +
                "\ti.name,c.userId,c.productType,c.face,c.productId, ");
        //默认不分销卡地区
        if (StringUtils.isBlank(reportQueryParam.getArea())) {
            sql.append("'全部'");
        } else {
            sql.append("if(c.supplyArea = 0, '全国', c.area)");
        }
        sql.append(" area,c.settlePrice FROM yuhuang_supply_card c ,\n" +
                "\t\t\tyuhuang_sup_user u,\n" +
                "\t\t\tyuhuang_sup_info i\n" +
                "\t\tWHERE\n" +
                "\t\t\tc.userId = u.user_id\n" +
                "\t\tAND u.user_id = i.user_id AND " +
                "c.endTime >= #{reportQueryParam.startTime} AND " +
                "c.endTime < #{reportQueryParam.endTime}  AND c.status = 1 ");
        //会员
        if (StringUtils.isNotBlank(reportQueryParam.getUserAccount())) {
            sql.append(" and (instr(u.account,#{reportQueryParam.userAccount})>0 or instr(i.name,#{reportQueryParam.userAccount})>0) ");
        }
        //面值
        if (reportQueryParam.getFace() != null) {
            sql.append(" and c.face = #{reportQueryParam.face} ");
        }
        //运营商
        if (reportQueryParam.getProductType() != null) {
            sql.append(" and c.productType = #{reportQueryParam.productType} ");
        }
        //销卡地区
        if (StringUtils.isNotBlank(reportQueryParam.getArea())) {
            if (StringUtils.isNumeric(reportQueryParam.getArea())) {
                sql.append(" and c.supplyArea = #{reportQueryParam.area} ");
            } else {
                sql.append(" and c.supplyArea = 1 and c.area = #{reportQueryParam.area} ");
            }
        }
        sql.append(") t1 GROUP BY t1.userId, t1.productType, t1.face, t1.area");
    }

    /**
     * 收卡销卡统计
     *
     * @param params 查询bean
     * @return sql
     */
    public String actualSupplyCardReportPage(HashMap<String, Object> params) {
        StringBuilder sql = new StringBuilder(100);
        sql.append("SELECT t1.account userAccount,\n" +
                "\tt1. NAME userName,t1.userId,t1.productType,t1.face,t1.area,t1.productId,sum(t1.settlePrice) sumFace ");
        generateReportActualSupplyCardSql(sql, params);
        sql.append(" ORDER BY t1.userId, t1.productType, t1.area, t1.face ");
        Page page = (Page) params.get("pageUnit");
        return SQLWrapUtils.wrapPage(sql, page.getBeginIndex(), page.getSize());
    }
}
